package com.gsk.europepharma.salesReport.struts.actions;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import net.sf.json.JSONArray;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.hibernate.Query;
import org.hibernate.Session;

import com.gsk.europepharma.salesReport.hibernate.dao.tbSpBoSRSectoDAO;
import com.gsk.europepharma.salesReport.utils.UtilsSalesReport;

public class ExportGridExcelUGA  extends Action {
	
	public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
		
		// Variables de session
		HttpSession sessionObject = request.getSession();
		Integer cs_adm_pdt = (Integer) sessionObject.getAttribute("cs_adm_pdt");
		Integer cs_adm_rgpt = (Integer) sessionObject.getAttribute("cs_adm_rgpt");
		Integer cs_adm_prstn = (Integer) sessionObject.getAttribute("cs_adm_prstn");
		Integer cs_network = (Integer) sessionObject.getAttribute("cs_network");
		// Integer cs_month = (Integer) sessionObject.getAttribute("cs_month");
		// Fin de session
				
		String nivGeog = request.getParameter("nivGeog");
		
		String source = request.getParameter("source");
		Integer CS_SRC = Integer.parseInt(source);
		String unite = request.getParameter("unite");
		
		String idGeog = request.getParameter("idGeog");
		Integer idGeogInteger = Integer.parseInt(idGeog);
		
		response.reset();
        response.setHeader("Content-Disposition", "attachment; filename=\"" + "topUGA.xls" + "\"");
        
		HSSFWorkbook wb = new HSSFWorkbook();
	    HSSFSheet sheet = wb.createSheet("Top UGPN");

	    //Returns an object that handles instantiating concrete classes
	    CreationHelper helper = wb.getCreationHelper();

	    //Creates the top-level drawing patriarch.
	    Drawing drawing = sheet.createDrawingPatriarch();

	    HSSFFont font = wb.createFont();
	    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	    font.setColor(HSSFColor.WHITE.index);
	    
	    HSSFCellStyle cellStyle = wb.createCellStyle();
	    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	    cellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
	    cellStyle.setFont(font);
	    
	    HSSFCellStyle cellStylePeer = wb.createCellStyle();
	    cellStylePeer.setFillPattern(CellStyle.SOLID_FOREGROUND);
	    cellStylePeer.setFillForegroundColor(HSSFColor.WHITE.index);
	    
	    HSSFCellStyle cellStyleOdd = wb.createCellStyle();
	    cellStyleOdd.setFillPattern(CellStyle.SOLID_FOREGROUND);
	    cellStyleOdd.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);


	    HSSFRow row = sheet.createRow(0);
	    HSSFCell cell = row.createCell((short) 0);
	    cell.setCellValue("UGPN");
	    cell.setCellStyle(cellStyle);
	    sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) 0));
	    cell = row.createCell((short) 1);
	    cell.setCellValue("CM3");
	    cell.setCellStyle(cellStyle);
	    sheet.addMergedRegion(new Region(0, (short) 1, 0, (short) 2));
	    cell = row.createCell((short) 3);
	    cell.setCellValue("Mois");
	    cell.setCellStyle(cellStyle);
	    sheet.addMergedRegion(new Region(0, (short) 3, 0, (short) 4));
	    
	    row = sheet.createRow(1);
	    cell = row.createCell((short) 1);
	    cell.setCellValue("PM");
	    cell.setCellStyle(cellStyle);
	    cell = row.createCell((short) 2);
	    cell.setCellValue("\u0394 PM");
	    cell.setCellStyle(cellStyle);
	    cell = row.createCell((short) 3);
	    cell.setCellValue("PM");
	    cell.setCellStyle(cellStyle);
	    cell = row.createCell((short) 4);
	    cell.setCellValue("\u0394 PM");
	    cell.setCellStyle(cellStyle);
	    
	    // Requete SQL
	    tbSpBoSRSectoDAO salesDAO = new tbSpBoSRSectoDAO();
		Session session = salesDAO.getSession();
		Query req = null;
		String strReq = "";
		strReq = salesDAO.QUERY_TOP_UGA_ALL;
		req = session.getNamedQuery(strReq);
		req.setParameter("data1",		unite + "CC");
		req.setParameter("data2",		unite + "MM");
		req.setInteger("cs_adm_pdt",		cs_adm_pdt);
		req.setInteger("cs_adm_rgpt",	cs_adm_rgpt);
		req.setInteger("cs_adm_prstn",	cs_adm_prstn);
		req.setInteger("CS_SRC",		CS_SRC);
		req.setInteger("cs_network",	cs_network);
		req.setString("nivGeog",		nivGeog);
		req.setInteger("idGeog",		idGeogInteger); 
		ArrayList result = new ArrayList(req.list());
		Iterator iter = result.iterator();
	 		
		JSONArray jsonArrayLines = new JSONArray();
		
		int i = 2;
		
		String tmpVal = "";
		
		HSSFCellStyle cellStyleLine;
		int j = 0;
		
		while (iter.hasNext()) {
			
			if ((j % 2) == 0) {
				cellStyleLine = cellStylePeer;
			} else {
				cellStyleLine = cellStyleOdd;
			}
			j++;
			
			row = sheet.createRow(i);
			
			Object[] val = (Object[]) iter.next();
			
			Double vol = 0.0;
			if (val[2] != null) {
				vol = Double.parseDouble(val[2].toString());
			}
			Double vol_1 = 0.0;
			if (val[3] != null) {
				vol_1 = Double.parseDouble(val[3].toString());
			}
			Double vol_tt = 0.0;
			if (val[4] != null) {
				vol_tt = Double.parseDouble(val[4].toString());
			}
			Double vol_tt_1 = 0.0;
			if (val[5] != null) {
				vol_tt_1 = Double.parseDouble(val[5].toString());
			}
			Double vol2 = 0.0;
			if (val[6] != null) {
				vol2 = Double.parseDouble(val[6].toString());
			}
			Double vol2_1 = 0.0;
			if (val[7] != null) {
				vol2_1 = Double.parseDouble(val[7].toString());
			}
			Double vol2_tt = 0.0;
			if (val[8] != null) {
				vol2_tt = Double.parseDouble(val[8].toString());
			}
			Double vol2_tt_1 = 0.0;
			if (val[9] != null) {
				vol2_tt_1 = Double.parseDouble(val[9].toString());
			}
			Double pmCm3 = null;
			if (val[2] != null && val[4] != null && vol_tt != 0.0) {
				pmCm3 = UtilsSalesReport.getPm(vol, vol_tt, 1);
			}
			Double pmCm3Delta = null;
			if (val[2] != null && val[3] != null && val[4] != null && val[5] != null && vol_tt != 0.0 && vol_tt_1 != 0.0) {
				pmCm3Delta = UtilsSalesReport.getDeltaPm(vol, vol_1, vol_tt, vol_tt_1, 1);
			}
			Double pm = null;
			if (val[6] != null && val[8] != null && vol2_tt != 0.0) {
				pm = UtilsSalesReport.getPm(vol2, vol2_tt, 1);
			}
			Double pmDelta = null;
			if (val[6] != null && val[7] != null && val[8] != null && val[9] != null && vol2_tt != 0.0 && vol2_tt_1 != 0.0) {
				pmDelta = UtilsSalesReport.getDeltaPm(vol2, vol2_1, vol2_tt, vol2_tt_1, 1);
			}
			
			cell = row.createCell((short) 0);
			cell.setCellValue(val[1].toString());
			cell.setCellStyle(cellStyleLine);

			cell = row.createCell((short) 1);
			if (pmCm3 != null) {
				cell.setCellValue(pmCm3 + "%");
			} else {
				cell.setCellValue("");
			}
			cell.setCellStyle(cellStyleLine);
			
			ClientAnchor anchor = helper.createClientAnchor();
		    anchor.setCol1(2);
		    anchor.setRow1(i);
		    Picture pict;
		    if (pmCm3Delta != null) {
		    	pict = drawing.createPicture(anchor, convertDeltaPmInIcon(pmCm3Delta, request, wb));
		    	pict.resize();
		    }
			
			cell = row.createCell((short) 2);
			if (pmCm3Delta != null) {
				cell.setCellValue(pmCm3Delta);
			} else {
				cell.setCellValue("");
			}
			cell.setCellStyle(cellStyleLine);
			
			cell = row.createCell((short) 3);
			if (pm != null) {
				cell.setCellValue(pm + "%");
			} else {
				cell.setCellValue("");
			}
			cell.setCellStyle(cellStyleLine);
			
			anchor = helper.createClientAnchor();
		    anchor.setCol1(4);
		    anchor.setRow1(i);
		    if (pmDelta != null) {
			    pict = drawing.createPicture(anchor, convertDeltaPmInIcon(pmDelta, request, wb));
			    pict.resize();
		    }
			
			cell = row.createCell((short) 4);
			if (pmDelta != null) {
				cell.setCellValue(pmDelta);
			} else {
				cell.setCellValue("");
			}
			cell.setCellStyle(cellStyleLine);
			
			i++;
		}
		
		sheet.autoSizeColumn(0);

	    try {
	      wb.write(response.getOutputStream());
	    } catch (FileNotFoundException e) {
	      e.printStackTrace();
	    } catch (IOException e) {
	      e.printStackTrace();
	    }
	    
        response.getOutputStream().flush();
	    
	    return null;
	}
	
	private int convertDeltaPmInIcon(Double deltaDouble, HttpServletRequest request, HSSFWorkbook wb) throws Exception {
	    String path = "";
		if (deltaDouble >= 0) {
			path = request.getRealPath("/img/cercle-vert.png");
		} else if (deltaDouble < -0.5) {
			path = request.getRealPath("/img/cercle-rouge.png");
		} else {
			path = request.getRealPath("/img/cercle-jaune.png");
		}
		InputStream inputStream = new FileInputStream(path);
	    byte[] bytes = IOUtils.toByteArray(inputStream);
	    int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
	    
		return pictureIdx;
	}

}
